﻿CREATE PROCEDURE [dbo].[SponsorEnrollment_Insert]

	@sponsorId			BIGINT,

	@parentSponsorId	BIGINT = NULL,

	@programId			BIGINT,

	@effectiveDate		DATE,

	@terminationDate	DATE,

	@enrollmentActionReasonId	BIGINT,

	@modifiedId			VARCHAR (0060)
	
AS

	BEGIN 

		DECLARE @existingEnrollmentId AS BIGINT

		DECLARE @existingEffectiveDate AS DATE

		DECLARE @existingTerminationDate AS DATE


		SELECT @existingEnrollmentId = Id, @existingEffectiveDate = EffectiveDate, @existingTerminationDate = TerminationDate
			
			FROM SponsorEnrollment

			WHERE 

				(SponsorEnrollment.SponsorId = @sponsorId) -- SAME SPONSOR 

				AND (ISNULL (SponsorEnrollment.ParentSponsorId, 0) = ISNULL (@parentSponsorId, 0)) -- SAME PARENT SPONSOR (OR BOTH DIRECT)

				AND (SponsorEnrollment.ProgramId = @programId) -- SAME PROGRAM 
				
				AND (SponsorEnrollment.EffectiveDate <= @terminationDate) AND (SponsorEnrollment.TerminationDate >= @effectiveDate) -- OVERLAPPING 

				AND (SponsorEnrollment.Voided = 0) -- AND NOT VOIDED 


		IF ((@existingEnrollmentId IS NULL) AND (@terminationDate >= @effectiveDate)) 

			BEGIN 

				INSERT INTO SponsorEnrollment 

					(SponsorId, ParentSponsorId, ProgramId, EffectiveDate, TerminationDate, Voided, EnrollmentActionReasonId, CreateId, ModifiedId)

					VALUES (@sponsorId, @parentSponsorId, @programId, @effectiveDate, @terminationDate, 0, @enrollmentActionReasonId, @modifiedId, @modifiedId)

			END 


		RETURN 0;
		
	END

	